<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class pendidikan_model extends CI_model {

    function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function getChart($id_kec = 0, $id_kel = 0) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select('sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, sum(jumlah) as jumlah, kode_pendidikan as kategori_id, nama_pendidikan as kategori');
            $this->db->where('nama_pendidikan is not null');
            $this->db->group_by('kode_pendidikan')
                    ->order_by('jumlah desc')->order_by('kode_pendidikan');
            $query = $this->db->get('penduduk_per_pendidikan');
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select('sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, sum(jumlah) as jumlah, kode_pendidikan as kategori_id, nama_pendidikan as kategori');
            $this->db->where('nama_pendidikan is not null');
            $this->db->where($filter);
            $this->db->group_by('kode_pendidikan')
                    ->order_by('jumlah desc')->order_by('kode_pendidikan');
            $query = $this->db->get('penduduk_per_pendidikan');
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->select('sum(jumlah_laki) as jumlah_laki, sum(jumlah_perempuan) as jumlah_perempuan, sum(jumlah) as jumlah, kode_pendidikan as kategori_id, nama_pendidikan as kategori');
            $this->db->where('nama_pendidikan is not null');
            $this->db->where(array('kode_kecamatan' => $filter['kode_kecamatan']));
            $this->db->group_by('kode_pendidikan')
                    ->order_by('jumlah desc')->order_by('kode_pendidikan');
            $query = $this->db->get('penduduk_per_pendidikan');
        }
        //echo $this->db->last_query();
        return $query->result();
    }

    function getChartPesertaDidik($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select('sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kecamatan as kategori, kec_kode as kategori_id');
            $this->db->group_by('kec_kode')
                    ->order_by('kec_kode');
            $query = $this->db->get('pendidikan_pesertadidik_berdasarkan_jeniskelamin');
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select('sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kecamatan as kategori, kec_kode as kategori_id');

            $this->db->where($filter);
            $this->db->group_by('kec_kode')
                    ->order_by('kec_kode desc');
            $query = $this->db->get('pendidikan_pesertadidik_berdasarkan_jeniskelamin');
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->select('sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kecamatan as kategori, kec_kode as kategori_id');

            $this->db->where(array('kec_kode' => $filter['kode_kecamatan']));
            $this->db->group_by('kec_kode')
                    ->order_by('kec_kode');
            $query = $this->db->get('pendidikan_pesertadidik_berdasarkan_jeniskelamin');
        }
        //echo $this->db->last_query();
        return $query->result();
    }

    function getChartPtkAgama($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select('sum(islam) as Islam, sum(kristen) as Kristen, sum(khatolik) as Katolik, sum(hindu) as Hindu, sum(budha) as Budha, sum(konghucu) as Konghucu, sum(lainnya) as Lainnya, sum(`TIDAK DIISI`)as `Tidak Diisi`');

            $query = $this->db->get('pendidikan_ptk_per_agama');
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select('sum(islam) as Islam, sum(kristen) as Kristen, sum(khatolik) as Katolik, sum(hindu) as Hindu, sum(budha) as Budha, sum(konghucu) as Konghucu, sum(lainnya) as Lainnya, sum(`TIDAK DIISI`)as `Tidak Diisi`');

            $this->db->where($filter);
            $this->db->group_by('kec_kode')
                    ->order_by('kec_kode desc');
            $query = $this->db->get('pendidikan_ptk_per_agama');
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->select('sum(islam) as Islam, sum(kristen) as Kristen, sum(khatolik) as Katolik, sum(hindu) as Hindu, sum(budha) as Budha, sum(konghucu) as Konghucu, sum(lainnya) as Lainnya, sum(`TIDAK DIISI`)as `Tidak Diisi`');

            $this->db->where(array('kec_kode' => $filter['kode_kecamatan']));

            $query = $this->db->get('pendidikan_ptk_per_agama');
        }
        //echo $this->db->last_query();
        return $query->result();
    }

    function getChartJenisPtk($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select('sum(guru_kelas) as `Guru Kelas`, sum(guru_mapel) as `Guru Mapel`, sum(guru_bk) as `Guru BK`, sum(guru_inklusi) as `Guru Inklusi`, sum(tenaga_adm_sekolah) as `Tenaga Administrasi Sekolah`, sum(guru_pendamping) as `Guru Pendamping`, sum(guru_magang) as `Guru Magang`, sum(guru_tik) as `Guru TIK`, sum(laboran) as `Laboran`, sum(pustakawan) as `Pustakawan`, sum(lainnya) as `Lainnya`');

            $query = $this->db->get('pendidikan_ptk_per_jenis_ptk');
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select('sum(guru_kelas) as `Guru Kelas`, sum(guru_mapel) as `Guru Mapel`, sum(guru_bk) as `Guru BK`, sum(guru_inklusi) as `Guru Inklusi`, sum(tenaga_adm_sekolah) as `Tenaga Administrasi Sekolah`, sum(guru_pendamping) as `Guru Pendamping`, sum(guru_magang) as `Guru Magang`, sum(guru_tik) as `Guru TIK`, sum(laboran) as `Laboran`, sum(pustakawan) as `Pustakawan`, sum(lainnya) as `Lainnya`');

            $this->db->where($filter);

            $query = $this->db->get('pendidikan_ptk_per_jenis_ptk');
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->select('sum(guru_kelas) as `Guru Kelas`, sum(guru_mapel) as `Guru Mapel`, sum(guru_bk) as `Guru BK`, sum(guru_inklusi) as `Guru Inklusi`, sum(tenaga_adm_sekolah) as `Tenaga Administrasi Sekolah`, sum(guru_pendamping) as `Guru Pendamping`, sum(guru_magang) as `Guru Magang`, sum(guru_tik) as `Guru TIK`, sum(laboran) as `Laboran`, sum(pustakawan) as `Pustakawan`, sum(lainnya) as `Lainnya`');

            $this->db->where(array('kec_kode' => $filter['kode_kecamatan']));

            $query = $this->db->get('pendidikan_ptk_per_jenis_ptk');
        }
        //echo $this->db->last_query();
        return $query->result();
    }

    function getChartStatusPtk($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select('sum(`PNS`) as `PNS`,'
                    . 'sum(`PNS DIPERBANTUKAN`) as `PNS DIPERBANTUKAN`, '
                    . 'sum(`PNS DEPAG`) as	`PNS DEPAG`, '
                    . 'sum(`CPNS`) as	`CPNS`, '
                    . 'sum(`GTY_PTY`) as	`GTY PTY`, '
                    . 'sum(`GTT_PTT_PROVINSI`) as 	`GTT PTT PROVINSI`, '
                    . 'sum(`GTT_PTT_KABKOTA`) as 	`GTT PTT KABKOTA`, '
                    . 'sum(`GURU_BANTU_PUSAT`) as	`GURU BANTU PUSAT`, '
                    . 'sum(`GURU_HONOR_SEKOLAH`) as	`GURU HONOR SEKOLAH`, '
                    . 'sum(`TENAGA_HONOR_SEKOLAH`) as	`TENAGA HONOR SEKOLAH`, '
                    . 'sum(`LAINNYA`) as	`LAINNYA`');

            $query = $this->db->get('pendidikan_ptk_per_statuskepegawaian');
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select('sum(`PNS`) as `PNS`,'
                    . 'sum(`PNS DIPERBANTUKAN`) as `PNS DIPERBANTUKAN`, '
                    . 'sum(`PNS DEPAG`) as	`PNS DEPAG`, '
                    . 'sum(`CPNS`) as	`CPNS`, '
                    . 'sum(`GTY_PTY`) as	`GTY PTY`, '
                    . 'sum(`GTT_PTT_PROVINSI`) as 	`GTT PTT PROVINSI`, '
                    . 'sum(`GTT_PTT_KABKOTA`) as 	`GTT PTT KABKOTA`, '
                    . 'sum(`GURU_BANTU_PUSAT`) as	`GURU BANTU PUSAT`, '
                    . 'sum(`GURU_HONOR_SEKOLAH`) as	`GURU HONOR SEKOLAH`, '
                    . 'sum(`TENAGA_HONOR_SEKOLAH`) as	`TENAGA HONOR SEKOLAH`, '
                    . 'sum(`LAINNYA`) as	`LAINNYA`');

            $this->db->where($filter);

            $query = $this->db->get('pendidikan_ptk_per_statuskepegawaian');
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->select('sum(`PNS`) as `PNS`,'
                    . 'sum(`PNS DIPERBANTUKAN`) as `PNS DIPERBANTUKAN`, '
                    . 'sum(`PNS DEPAG`) as	`PNS DEPAG`, '
                    . 'sum(`CPNS`) as	`CPNS`, '
                    . 'sum(`GTY_PTY`) as	`GTY PTY`, '
                    . 'sum(`GTT_PTT_PROVINSI`) as 	`GTT PTT PROVINSI`, '
                    . 'sum(`GTT_PTT_KABKOTA`) as 	`GTT PTT KABKOTA`, '
                    . 'sum(`GURU_BANTU_PUSAT`) as	`GURU BANTU PUSAT`, '
                    . 'sum(`GURU_HONOR_SEKOLAH`) as	`GURU HONOR SEKOLAH`, '
                    . 'sum(`TENAGA_HONOR_SEKOLAH`) as	`TENAGA HONOR SEKOLAH`, '
                    . 'sum(`LAINNYA`) as	`LAINNYA`');

            $this->db->where(array('kec_kode' => $filter['kode_kecamatan']));

            $query = $this->db->get('pendidikan_ptk_per_statuskepegawaian');
        }
        //echo $this->db->last_query();
        return $query->result();
    }

    function getChartBentukPendidikan($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select('sum(`SMA`) as `SMA` , '
                    . 'sum(`SMP` ) as `SMP` , '
                    . 'sum(`SMLB`) as	`SMLB`');

            $query = $this->db->get('pendidikan_sekolah_per_bentukpendidikan');
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select('sum(`SMA`) as `SMA` , '
                    . 'sum(`SMP` ) as `SMP` , '
                    . 'sum(`SMLB`) as	`SMLB`');

            $this->db->where($filter);

            $query = $this->db->get('pendidikan_sekolah_per_bentukpendidikan');
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->select('sum(`SMA`) as `SMA` , '
                    . 'sum(`SMP` ) as `SMP` , '
                    . 'sum(`SMLB`) as	`SMLB`');

            $this->db->where(array('kec_kode' => $filter['kode_kecamatan']));

            $query = $this->db->get('pendidikan_sekolah_per_bentukpendidikan');
        }
        //echo $this->db->last_query();
        return $query->result();
    }

    function getChartPtkJenisKelamin($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select('sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kecamatan as kategori, kec_kode as kategori_id');
            $this->db->group_by('kec_kode')
                    ->order_by('kec_kode');
            $query = $this->db->get('pendidikan_ptk_per_jeniskelamin');
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select('sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kecamatan as kategori, kec_kode as kategori_id');

            $this->db->where($filter);
            $this->db->group_by('kec_kode')
                    ->order_by('kec_kode desc');
            $query = $this->db->get('pendidikan_ptk_per_jeniskelamin');
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->select('sum(laki) as jumlah_laki, sum(perempuan) as jumlah_perempuan, kecamatan as kategori, kec_kode as kategori_id');

            $this->db->where(array('kec_kode' => $filter['kode_kecamatan']));
            $this->db->group_by('kec_kode')
                    ->order_by('kec_kode');
            $query = $this->db->get('pendidikan_ptk_per_jeniskelamin');
        }
        //echo $this->db->last_query();
        return $query->result();
    }

    function getChartStatusSekolah($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select('negeri, swasta, kecamatan as kategori, kec_kode as kategori_id');
            $this->db->order_by('kec_kode');
            $query = $this->db->get('pendidikan_sekolah_per_status');
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select('negeri, swasta, kecamatan as kategori, kec_kode as kategori_id');

            $this->db->where($filter);
            $this->db
                    ->order_by('kec_kode desc');
            $query = $this->db->get('pendidikan_sekolah_per_status');
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->select('negeri, swasta, kecamatan as kategori, kec_kode as kategori_id');

            $this->db->where(array('kec_kode' => $filter['kode_kecamatan']));
            $this->db
                    ->order_by('kec_kode');
            $query = $this->db->get('pendidikan_sekolah_per_status');
        }
        //echo $this->db->last_query();
        return $query->result();
    }

    public function get_jumlah_data_by_pendidikan($kunci, $id_kec, $id_kel) {
        $sql = "select * from pddkn_master where no=$kunci";
        $query = $this->db->query($sql);
        $filter = array();
        foreach ($query->result() as $key => $row) {
            $filter[] = "PDDK_AKH=$kunci";
            break;
        }

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select count(*) as jumlah from biodata_wni_bgt  "
                . " $s_filter ";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->result()[0]->jumlah;
    }

    function get_data_penduduk_by_pendidikan($kunci, $limit, $halaman, $id_kec, $id_kel) {
        $sql = "select * from pddkn_master where no=$kunci";
        $query = $this->db->query($sql);
        $filter = array();
        foreach ($query->result() as $key => $row) {
            $filter[] = "PDDK_AKH=$kunci";
            break;
        }

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select *,PDDK_AKH as kategori_id from biodata_wni_bgt  "
                . " $s_filter "
                . "limit $halaman, $limit";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_list_pendidikan() {
        $sql = "select NO as kategori_id, DESCRIP as kategori from pddkn_master";
        $query = $this->db->query($sql);
        return $query->result();
    }

}
